Have you ever wanted or had the need to take a database table and cram a lot of information into a specific field using JSON?
This is possible and supported by both PostgreSQL and Ecto, and it can sometimes simplify your database in cases where you absolutely want to store some kind of nested data structure.
This post shortly describes how to create embedded schemas in Ecto to save a JSON data structure on your database, and how to implement a direct database search on this same field.
PostgreSQL has great support for objects stored as JSON. This is useful for those moments when you need to store data that could be variably structured, such as responses from other services’ APIs, or data that frequently travels together within your relational tables.
A common trade-off for mixing scalar column data types (like varchar or integer) with column data types that handle more-complicated objects (like JSON) is that ORMs or data mappers sometimes can’t introspect on them for you, which means it becomes much harder to query that data.
Using Ecto’s embedded_schema helps introspect on those known values, but it doesn’t really assist you with querying those fields in SQL. This is where I became extremely greatful for Ecto’s escape hatch: fragment().